﻿Imports Microsoft.Office.Interop
Public Class cExcel
    Dim lds As DataSet

    Public Sub New(ByVal ds As DataSet)
        lds = New DataSet
        lds = ds
    End Sub

    Public Function exportarExcel() As Object
        Try
            Dim m_Excel
            Dim objLibroExcel
            Dim objHojaExcel
            m_Excel = CreateObject("Excel.Application")
            objLibroExcel = m_Excel.Workbooks.Add()
            objHojaExcel = objLibroExcel.Worksheets(1)
            objHojaExcel.Name = "Hoja1"
            objHojaExcel.Visible = Excel.XlSheetVisibility.xlSheetVisible
            objHojaExcel.Activate()

            '/////////////////////////////////////////////////////////
            '// Definimos dos variables para controlar fila y columna
            '/////////////////////////////////////////////////////////
            Dim fila As Integer = 1
            Dim columna As Integer = 1

            '/////////////////////////////////////////////////
            '// Armamos la linea con los títulos de columnas
            '/////////////////////////////////////////////////
            objHojaExcel.Range("A1").Select()
            For Each dc In lds.Tables(0).Columns
                objHojaExcel.Range(nombreColumna(columna) & 1).Value = dc.ColumnName
                columna += 1
            Next
            fila += 1

            '/////////////////////////////////////////////
            '// Le damos formato a la fila de los títulos
            '/////////////////////////////////////////////
            Dim objRango As Excel.Range = objHojaExcel.Range("A1:" & nombreColumna(lds.Tables(0).Columns.Count) & "1")
            objRango.Font.Bold = True
            objRango.Cells.Interior.ColorIndex = 35

            objRango.Cells.Borders(Excel.XlBordersIndex.xlDiagonalDown).LineStyle = Excel.XlLineStyle.xlLineStyleNone
            objRango.Cells.Borders(Excel.XlBordersIndex.xlDiagonalUp).LineStyle = Excel.XlLineStyle.xlLineStyleNone
            objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone
            objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous
            objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous
            objRango.Cells.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous

            '//////////////////////////////////////////
            '// Cargamos todas las filas del datatable
            '//////////////////////////////////////////
            'ProgressBar1.Maximum = ds.Tables(0).Rows.Count
            'columna = 1
            'ProgressBar1.Value = 0
            For Each dr In lds.Tables(0).Rows
                columna = 1
                For Each dc In lds.Tables(0).Columns
                    objHojaExcel.Range(nombreColumna(columna) & fila).Value = dr(dc.ColumnName)
                    columna += 1
                Next
                fila += 1

            Next

            '//////////////////////////////////////
            '// Ajustamos automaticamente el ancho
            '// de todas las columnas utilizada
            '//////////////////////////////////////
            objRango = objHojaExcel.Range("A1:" & nombreColumna(lds.Tables(0).Columns.Count) & lds.Tables(0).Rows.Count.ToString)
            objRango.Select()
            objRango.Columns.AutoFit()

            '/////////////////////////////////////
            '// Le decimos a Excel que se muestre
            '/////////////////////////////////////
            Return m_Excel
            'MsgBox("Exportación a Excel completa", MsgBoxStyle.Information, ".:: solovb.net ::.")
            'm_Excel.Visible = True

        Catch ex As Exception
            Throw ex
        End Try
    End Function


    Public Function nombreColumna(ByVal numero As Integer) As String
        Dim columna(256) As String
        Try
            columna(1) = "A"
            columna(2) = "B"
            columna(3) = "C"
            columna(4) = "D"
            columna(5) = "E"
            columna(6) = "F"
            columna(7) = "G"
            columna(8) = "H"
            columna(9) = "I"
            columna(10) = "J"
            columna(11) = "K"
            columna(12) = "L"
            columna(13) = "M"
            columna(14) = "N"
            columna(15) = "O"
            columna(16) = "P"
            columna(17) = "Q"
            columna(18) = "R"
            columna(19) = "S"
            columna(20) = "T"
            columna(21) = "U"
            columna(22) = "V"
            columna(23) = "W"
            columna(24) = "X"
            columna(25) = "Y"
            columna(26) = "Z"
            columna(27) = "AA"
            columna(28) = "AB"
            columna(29) = "AC"
            columna(30) = "AD"
            columna(31) = "AE"
            columna(32) = "AF"
            columna(33) = "AG"
            columna(34) = "AH"
            columna(35) = "AI"
            columna(36) = "AJ"
            columna(37) = "AK"
            columna(38) = "AL"
            columna(39) = "AM"
            columna(40) = "AN"
            columna(41) = "AO"
            columna(42) = "AP"
            columna(43) = "AQ"
            columna(44) = "AR"
            columna(45) = "AS"
            columna(46) = "AT"
            columna(47) = "AU"
            columna(48) = "AV"
            columna(49) = "AW"
            columna(50) = "AX"
            Return (columna(numero))
        Catch ex As Exception
            Throw ex
        End Try
    End Function

End Class

Public Class ExportaExcelXML

    Public Sub New()
    End Sub
End Class